package com.kgd.news.utils.db;

import com.jfinal.log.Log;
import com.kgd.news.entity.Adv;
import com.kgd.news.entity.Category;
import com.kgd.news.utils.StringUtilsWx;

import java.sql.*;
import java.util.*;

public class DbSychronizationController {
    //    //同步源
//    static  String url_source="jdbc:mysql://localhost:3306/zntz?user=root&password=123456&useUnicode=true&characterEncoding=UTF8";
//    //目标库
//    static String url_destination="jdbc:mysql://localhost:3306/xx01?user=root&password=123456&useUnicode=true&characterEncoding=UTF8";
    static Connection conn_source = null;
    static Connection conn_destination = null;
    static String sql_read;
    static String sql_insert;
    static final int batchSize = 15000;
    private final static Log log = Log.getLog(DbSychronizationController.class);

    static final int max_thread_size = 5;

    public static void writeData() {
    }

    /**
     * @param url_source
     * @param url_destination
     * @param tableName       要插入的表的表名
     * @throws SQLException
     * @throws InterruptedException
     */
    public static void init(String url_source, String url_destination, String content_ids, String tableName, String type, String url_clear, List<Adv> advList, String ui_checkbox) throws SQLException, InterruptedException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn_source = DriverManager.getConnection(url_source);
            conn_destination = DriverManager.getConnection(url_destination);
            conn_destination.setAutoCommit(false);
            synchronizationTables(conn_source, conn_destination);
            if (!StringUtilsWx.isEmpty(tableName)) {
//                同步所有的表
                if ("sychronize_all".equals(tableName)) {
                    addData(conn_source, conn_destination, url_clear);
                } else {
//如果时类别是类型管理，并且没有选中选择框的
                    if ("category".equals(tableName) && "false".equals(ui_checkbox)) {
                        addData(url_clear, conn_source, conn_destination);
                        return;
                    }
//                    type再copyTable里就是tablename
                    if (!StringUtilsWx.isEmpty(type) && type.equals(tableName)) {
//                        拷贝整个表，type不为空就是整表复制的
                        addData(conn_source, conn_destination, tableName, url_clear);
                    } else if (!StringUtilsWx.isEmpty(type) && "copyByGender".equals(type)) {
//copyType:'copyByGender',就是通过性别拷贝吧
                        addData(tableName, conn_destination, advList, url_clear, type);
                    } else if (!StringUtilsWx.isEmpty(type) && "copyByGenderCategory".equals(type)) {
//copyType:'copyByGender',copyByGenderCategory
                        addData(tableName, conn_destination, advList, url_clear, type);
                    } else if (!StringUtilsWx.isEmpty(type) && "copyByGenderCategoryType".equals(type)) {
//copyType:'copyByGender'，copyByGenderCategoryType，通过性别类型，类别吧
                        addData(tableName, conn_destination, advList, url_clear, type);
                    } else {
                        addData(conn_source, conn_destination, content_ids, tableName, url_clear);
                    }
                }
            } else {
//                拷贝整个表，type不为空就是整表复制的
                if (!StringUtilsWx.isEmpty(type)) {
                    addData(conn_source, conn_destination, type, url_clear);
                }
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } finally {
            while (true) {
                if (InsertThread.getThreadCounts() > 0) {
                    Thread.sleep(20);
                } else {
                    break;
                }
            }
            if (conn_source != null) {
                conn_source.close();
            }
            if (conn_destination != null) {
                conn_destination.close();
            }

        }

    }


    //本地获取表名获取表名
    public static Set<String> getTableName(Connection con) {
        Set<String> set = new HashSet<String>();
        try {
            DatabaseMetaData meta = con.getMetaData();
            ResultSet rs = meta.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                set.add(rs.getString("TABLE_NAME"));
//                String s = rs.getString("TABLE_NAME");
//                String type = rs.getString("TABLE_TYPE");
//                System.out.println(s+"======"+type);
//                getTableDDL(rs.getString("TABLE_NAME"), con);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return set;
    }

    //目标数据库
    public static Map<String, String> getTableNameToMap(Connection con) {
        Map<String, String> map = new HashMap<String, String>();
        try {
            DatabaseMetaData meta = con.getMetaData();
            ResultSet rs = meta.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                map.put(rs.getString("TABLE_NAME"), "1");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    //创建表
    public static void createTable(String sql_ddl) throws SQLException {
        Statement stmt = conn_destination.createStatement();
        int result = stmt.executeUpdate(sql_ddl);// executeUpdate语句会返回一个受影响的行数，如果返回-1就没有成功
        if (result != -1) {
            log.info("表创建成功");
        } else {
            log.error("表创建失败：" + sql_ddl);
        }
    }

    //创建sql
    public static String getTableField(String tableName, Connection con, boolean excludeId) throws SQLException {
        String sql = "select * from " + tableName;
        Statement state = con.createStatement();
        ResultSet rs = state.executeQuery(sql);
        ResultSetMetaData rsd = rs.getMetaData();
        StringBuffer sql_model = new StringBuffer("insert into " + tableName + " (");
        StringBuffer sql_param = new StringBuffer(" VALUES(");
        for (int i = 1; i <= rsd.getColumnCount(); i++) {
            if (excludeId) {
                if (i == 1) {
                    continue;
                }
            }
            sql_model.append("`" + rsd.getColumnName(i) + "`");
            sql_param.append("?");
            if (i < rsd.getColumnCount()) {
                sql_model.append(",");
                sql_param.append(",");
            }
        }
        sql_model.append(") ");
        sql_param.append(") ");
        System.out.println(sql_model.toString() + sql_param.toString());
        return sql_model.toString() + sql_param.toString();
    }


    public static void getTableField2(String tableName, Connection conn) throws SQLException {

        ResultSet rs = conn.getMetaData().getColumns(null, conn.getMetaData().getUserName(), tableName.toUpperCase(), "%");

        while (rs.next()) {
            String colName = rs.getString("COLUMN_NAME");
            String remarks = rs.getString("REMARKS");
            String dbType = rs.getString("TYPE_NAME");

            System.out.println(colName + "," + remarks + "," + dbType);
        }
    }

    //获取表结构ddl
    public static String getTableDDL(String tableName, Connection conn) throws SQLException {
        ResultSet rs = null;
        PreparedStatement ps = null;
        ps = conn.prepareStatement("show create table " + tableName);
        rs = ps.executeQuery();
        StringBuffer ddl = new StringBuffer();
        while (rs.next()) {
            ddl.append(rs.getString(rs.getMetaData().getColumnName(2)));
        }
        return ddl.toString();
    }

    /**
     * 检查本地库所有表在B库里是否存在，是否一致
     * A本地库  B目标库
     */
    public static void synchronizationTables(Connection conA, Connection conB) throws SQLException {
        Set<String> a_set = getTableName(conA);
        Map<String, String> b_map = getTableNameToMap(conB);

        Iterator<String> it = a_set.iterator();
        while (it.hasNext()) {
            String n = it.next();
            if (b_map.get(n) == null) {
                System.out.println("表名：" + n + "   不在目标库里");
                String create_table_ddl = getTableDDL(n, conA);
                createTable(create_table_ddl);
            }
        }
    }


    /**
     * 检查本地库所有表在B库里是否存在，是否一致
     * A本地库  B目标库
     */
    public static void synchronizationTables(String tableName, Connection conA, Connection conB) throws SQLException {
        Map<String, String> b_map = getTableNameToMap(conB);

        if (b_map.get(tableName) == null) {
            System.out.println("表名：" + tableName + "   不在目标库里");
            String create_table_ddl = getTableDDL(tableName, conA);
            createTable(create_table_ddl);
        }
    }

    //清楚表数据
    public static boolean clearTableData(String tableName, Connection con) {
        try {
            Statement stmt = con.createStatement();
            String sql = "TRUNCATE TABLE  " + tableName;
            stmt.executeUpdate(sql);
            System.out.println(tableName + ":表数据已被清空");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("异常表：" + tableName + "----数据清空失败");
            return false;
        }
        return true;
    }


    /**
     * @param conA
     * @param conB
     * @param content_ids
     * @param tableName
     * @param url_clear
     * @throws SQLException
     * @throws InterruptedException 复制表里面id在content_ids里面的
     */
    public static void addData(Connection conA, Connection conB, String content_ids, String tableName, String url_clear) throws SQLException, InterruptedException {
        Statement stmt_source = conA.createStatement();
        //遍历表
//        while (it.hasNext()) {
        long start = System.currentTimeMillis();
        String str = tableName;
        while (true) {
            if (InsertThread.getThreadCounts() > 0) {
                Thread.sleep(20);
            } else {
                break;
            }
        }
        if (str.equals("image_media")) {
            str = "image";
//还要同步media表里面otherid为该id的
            //            获取插入的sql语句,和具体的值没有关系的
            String sql_insert = getTableField("media", conA, false);
            String sql_delete = "delete  from " + "media" + " where otherid in (" + content_ids + ")";
            String sql_data = "select * from " + "media" + " where otherid in (" + content_ids + ")";
            Thread td = new InsertThread(sql_data, sql_insert, sql_delete, conB, conA, url_clear, str);
            td.start();
        } else if (str.equals("question")) {
//            还要同步option表，score表里面的questionid的东西
            //            获取插入的sql语句,和具体的值没有关系的
            String sql_insert = getTableField("options", conA, false);
            String sql_delete = "delete  from " + "options" + " where questionid in (" + content_ids + ")";
            String sql_data = "select * from " + "options" + " where questionid in (" + content_ids + ")";
            Thread td = new InsertThread(sql_data, sql_insert, sql_delete, conB, conA, url_clear, str);
            td.start();

            String sql_insert1 = getTableField("score", conA, false);
            String sql_data1 = "select * from " + "score" + " where questionid in (" + content_ids + ")";
            String sql_delete1 = "delete from " + "score" + " where questionid in (" + content_ids + ")";
            Thread td1 = new InsertThread(sql_data1, sql_insert1, sql_delete1, conB, conA, url_clear, str);
            td1.start();
        } else if (str.equals("category")) {
//            还要同步option表，score表里面的questionid的东西
            //            获取插入的sql语句,和具体的值没有关系的
            String sql_insert = getTableField("adv", conA, false);
            String sql_data = "select * from " + "adv" + " where categoryid in (" + content_ids + ")";
            String sql_delete = "delete from " + "adv" + " where categoryid in (" + content_ids + ")";
            Thread td = new InsertThread(sql_data, sql_insert, sql_delete, conB, conA, url_clear, str);
            td.start();
        }
        //            获取插入的sql语句,和具体的值没有关系的
        String sql_insert = getTableField(str, conA, false);
        //获取总条数 分页查询
        String sql_count = "select count(*) from " + str;
        ResultSet rs = stmt_source.executeQuery(sql_count);
        rs.next();
//            15000的数量，一般情况下达不到
        String sql_data = "select * from " + str + " where id in (" + content_ids + ")";
        String sql_delete = "delete from " + str + " where id in (" + content_ids + ")";
        Thread td = new InsertThread(sql_data, sql_insert, sql_delete, conB, conA, url_clear, str);
        td.start();
        long end = System.currentTimeMillis();
        log.info(str + "  表数据导入完成,耗时：" + (end - start) / 1000 + "秒," + (end - start) / 60000 + "分钟");
        System.out.println(str + "  表数据导入完成,耗时：" + (end - start) / 1000 + "秒," + (end - start) / 60000 + "分钟");
    }


    /**
     * @param conA
     * @param conB
     * @param url_clear
     * @throws SQLException
     * @throws InterruptedException 复制整个表
     */
    public static void addData(Connection conA, Connection conB, String url_clear) throws SQLException, InterruptedException {
        Statement stmt_source = conA.createStatement();
        Set<String> tableNameSet = getTableName(conn_source);
        Iterator<String> it = tableNameSet.iterator();
        //遍历表
        while (it.hasNext()) {
            long start = System.currentTimeMillis();
            String str = it.next();
            if ("xcxmessage".equals(str) || "sys_dict".equals(str) || "sys_user".equals(str) || "dbtable".equals(str)) {
                continue;
            }
            if (!clearTableData(str, conB)) {
                continue;
            }
//            这个同步方法可以，虽然开了心线程，但任务是一个一个做的，
            while (true) {
                if (InsertThread.getThreadCounts() > 0) {
                    Thread.sleep(50);
                } else {
                    break;
                }
            }
//            获取插入的sql语句,和具体的值没有关系的
            String sql_insert = getTableField(str, conA, false);
            //获取总条数 分页查询
            String sql_data = "select * from " + str;
            Thread td = new InsertThread(sql_data, sql_insert, "", conB, conA, url_clear, str);
            td.start();
            long end = System.currentTimeMillis();
            log.info(str + "  表数据导入完成,耗时：" + (end - start) / 1000 + "秒," + (end - start) / 60000 + "分钟");
        }
    }

    /**
     * @param conA
     * @param conB
     * @param tableName
     * @param url_clear
     * @throws SQLException
     * @throws InterruptedException 拷贝整个表
     */
    public static void addData(Connection conA, Connection conB, String tableName, String url_clear) throws SQLException, InterruptedException {
        //遍历表
        long start = System.currentTimeMillis();
//        这里
        String str = tableName;
        while (true) {
            if (InsertThread.getThreadCounts() > 0) {
                Thread.sleep(20);
            } else {
                break;
            }
        }
        if (str.equals("image_media")) {
//            str = "image";
//还要同步media表里面otherid为该id的
            //            获取插入的sql语句,和具体的值没有关系的
            String sql_insert = getTableField("media", conA, false);
            String sql_delete = "delete  from " + "media";
            String sql_data = "select * from " + "media";
            Thread td = new InsertThread(sql_data, sql_insert, sql_delete, conB, conA, url_clear, str);
            td.start();
        } else if (str.equals("question")) {
//            还要同步option表，score表里面的questionid的东西
            //            获取插入的sql语句,和具体的值没有关系的
            String sql_insert = getTableField("options", conA, false);
            String sql_delete = "delete  from " + "options";
            String sql_data = "select * from " + "options";
            Thread td = new InsertThread(sql_data, sql_insert, sql_delete, conB, conA, url_clear, str);
            td.start();

            String sql_insert1 = getTableField("score", conA, false);
            String sql_data1 = "select * from " + "score";
            String sql_delete1 = "delete from " + "score";
            Thread td1 = new InsertThread(sql_data1, sql_insert1, sql_delete1, conB, conA, url_clear, str);
            td1.start();
        } else if (str.equals("category")) {
//            还要同步option表，score表里面的questionid的东西
            //            获取插入的sql语句,和具体的值没有关系的
            String sql_insert = getTableField("adv", conA, false);
            String sql_data = "select * from " + "adv";
            String sql_delete = "delete from " + "adv";
            Thread td = new InsertThread(sql_data, sql_insert, sql_delete, conB, conA, url_clear, str);
            td.start();
        } else if (str.equals("smallapp_shop")) {
//            还要同步option表，score表里面的questionid的东西
            //            获取插入的sql语句,和具体的值没有关系的
            String sql_insert = getTableField("smallapp", conA, false);
            String sql_data = "select * from smallapp where categoryid = 54";
            String sql_delete = "delete from smallapp where categoryid = 54";
            Thread td = new InsertThread(sql_data, sql_insert, sql_delete, conB, conA, url_clear, str);
            td.start();
        } else if (str.equals("smallapp_smallapp")) {
//            还要同步option表，score表里面的questionid的东西
            //            获取插入的sql语句,和具体的值没有关系的
            String sql_insert = getTableField("smallapp", conA, false);
            String sql_data = "select * from smallapp where categoryid = 55";
            String sql_delete = "delete from smallapp where categoryid = 55";
            Thread td = new InsertThread(sql_data, sql_insert, sql_delete, conB, conA, url_clear, str);
            td.start();
        }
//            获取插入的sql语句,和具体的值没有关系的
        String sql_insert = "";
        //获取总条数 分页查询
        String sql_data = "";
        String sql_delete = "";
//            image表type 2为视频，1为图片
        if ("image_video".equals(str)) {
            sql_insert = getTableField("image", conA, false);
            sql_data = "select * from image where type = 2";
            sql_delete = "delete  from image where type = 2";
//            不能这样子搞呀，图片表全
        } else if ("image_media".equals(str)) {
            sql_insert = getTableField("image", conA, false);
            sql_data = "select * from image where type = 1";
            sql_delete = "delete  from image where type = 1";
        } else if ("smallapp_shop".equals(str)) {
        } else if ("smallapp_smallapp".equals(str)) {
        } else {
            sql_data = "select * from " + str;
            sql_insert = getTableField(str, conA, false);
            if (!clearTableData(str, conB)) {
                return;
            }
        }
        Thread td = new InsertThread(sql_data, sql_insert, sql_delete, conB, conA, url_clear, str);
        td.start();
        long end = System.currentTimeMillis();
        log.info(str + "  表数据导入完成,耗时：" + (end - start) / 1000 + "秒," + (end - start) / 60000 + "分钟");
    }

    private static void addData(String tableName, Connection conB, List<Adv> advList, String url_clear, String copyType) throws SQLException, InterruptedException {
//        这里Log
        String str = tableName;
        while (true) {
            if (InsertThread.getThreadCounts() > 0) {
                Thread.sleep(20);
            } else {
                break;
            }
        }
//        对于三者使用的总结： 1.如果要操作少量的数据用 = String
//　　　　　　　　　　              2.单线程操作字符串缓冲区 下操作大量数据 = StringBuilder
//　　　　　　　　　　              3.多线程操作字符串缓冲区 下操作大量数据 = StringBuffer
        for (Adv adv : advList
                ) {
            StringBuilder sql_adv_update = new StringBuilder("update adv set img='");
            int gender = adv.getGender();
            String img = adv.getImg();
            long categotyid = adv.getCategoryid();
            String wxAppid = adv.getWxappid();
            String wxPpurl = adv.getWxppurl();
            sql_adv_update.append(img + "', wxappid='" + wxAppid + "', wxppurl='" + wxPpurl);

            sql_adv_update.append("' where gender=" + gender);
//            如果按类别、类型复制，一条广告只跟新一条信息的
            if ("copyByGenderCategoryType".equals(copyType)) {
                sql_adv_update.append(" and categoryid ="+categotyid);
//             按照类型来复制
            }else if("copyByGenderCategory".equals(copyType)){
                StringBuilder stringBuilder = new StringBuilder();
                List<Category> categoryList = Category.dao.find(" select * from category where parentid= (select parentid from category where id =" + categotyid + ")");
                if (categoryList != null) {
                    for (Category category : categoryList
                            ) {
                        long id = category.getId();
                        stringBuilder.append(id + " ,");
                    }
                    stringBuilder.deleteCharAt(stringBuilder.length() - 1);
                }
                sql_adv_update.append(" and categoryid in (" + stringBuilder.toString() + ")");
            }
            Thread td = new InsertThread(conB, str, sql_adv_update.toString(), url_clear);
            td.start();
        }
    }

    /**
     * @param url_clear
     * @param conn_source
     * @param conn_destination
     * @throws SQLException
     * @throws InterruptedException
     * 复制类别管理里的和广告一起复制，
     */
    private static void addData(String url_clear, Connection conn_source, Connection conn_destination) throws SQLException, InterruptedException {
//        这里
        String str = "category";
        while (true) {
            if (InsertThread.getThreadCounts() > 0) {
                Thread.sleep(20);
            } else {
                break;
            }
        }
//category只复制conn_source里面有的，如果没有的话，不去复制，并且不改变原有的排序的参数，那么只能用update了吧。
//        先得到
        StringBuilder content_builder = new StringBuilder();
        List<Category> categoryList = Category.dao.find("select * from category");
        if (categoryList != null) {
            for (Category category:categoryList
                 ) {
              long id=  category.getId();
                content_builder.append(id + ",");
            }
            if (content_builder.length() > 0) {
                content_builder.deleteCharAt(content_builder.length() - 1);
            }
        }
        // TODO: 2018/1/24 现在只能是暂时覆盖了吧，要做到保留排序顺序的话，就下一步开发吧。
//        这个要conn_destination有的才可以的，
        String content_ids = content_builder.toString();
        if (!StringUtilsWx.isEmpty(content_ids)) {
            String sql_insert = getTableField("adv", conn_source, false);
            String sql_data = "select * from " + "adv" + " where categoryid in (" + content_ids + ")";
            String sql_delete = "delete from " + "adv" + " where categoryid in (" + content_ids + ")";
            Thread td = new InsertThread(sql_data, sql_insert, sql_delete, conn_destination, conn_source, url_clear, str);
            td.start();
// TODO: 2018/1/24 要想办法不去覆盖order参数吧，因为每一个order参数不同的 
            String sql_insert1 = getTableField("category", conn_source, false);
            String sql_data1 = "select * from " + "category" + " where id in (" + content_ids + ")";
            String sql_delete1 = "delete from " + "category" + " where id in (" + content_ids + ")";
            Thread td1 = new InsertThread(sql_data1, sql_insert1, sql_delete1, conn_destination, conn_source, url_clear, str);
            td1.start();
        }


    }

}
